use master
go

create database USERINFO
go

use USERINFO
go

create table stuinfo
(
stuNO char(10),
stuName nvarchar(10),
stuAge int,
stuAddress varchar(20),
stuSeat int,
StuSex int
)
insert into stuinfo values
('s2501','张秋利','20','美国硅谷',1,1),
('s2502','李斯文','18','湖北武汉',2,0),
('s2503','马文才','22','湖南长沙',3,1),
('s2504','欧阳俊雄','21','湖北武汉',4,0),
('s2505','梅超风','20','湖北武汉',5,1),
('s2506','梅旋风','19','美国硅谷',6,1),
('s2507','陈凤','20','美国硅谷',7,0)
create table stuexam
(
examNO int ,
stuNo char(10),
writtenExam int,
labExam int
)
insert into stuexam values
(1,'s2501',50,70),
(2,'s2502',60,65),
(3,'s2503',86,85),
(4,'s2504',40,80),
(5,'s2505',70,90),
(6,'s2506',85,90)

select * from stuinfo
select * from stuexam
-- 1.查询学生的姓名，年龄，笔试成绩和机试成绩
select
	si.stuNO,si.StuName,si.stuAge,se.writtenExam,se.labExam
from
	stuinfo si
join
	stuexam se
on
	si.stuno = se.stuno

-- 2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
select
	si.stuNO,si.stuName,se.writtenExam,se.labExam
from
	stuinfo si
join
	stuexam se
on
	se.writtenExam > 60 and se.labExam > 60 and si.stuNO = se.stuNo

-- 3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
select
	si.stuNO,si.stuName,se.writtenExam,se.labExam
from
	stuinfo si
left join
	stuexam se
on
	si.stuno = se.stuno

-- 4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
select 
	si.stuName,si.stuAge,se.writtenExam,se.labExam
from
	stuinfo si
join
	stuexam se 
on
	si.stuNO = se.stuNo
where	
	si.stuAge > 20
order by
	se.writtenExam 
desc

-- 5.查询男女生的机试平均分
SELECT
	si.stuSex 性别,AVG(se.LabExam) 成绩
FROM
	stuinfo si
JOIN
	stuexam se
ON
	si.stuNO = se.stuNO

GROUP BY
	stuSex

-- 6.查询男女生的笔试总分
SELECT
	si.stuSex 性别,SUM(se.writtenExam) 成绩
FROM
	stuinfo si
JOIN
	stuexam se
ON
	si.stuNO = se.stuNO
GROUP BY
	stuSex



























